package com.hsnn.medstgmini.trading.pur.dao.impl;

import java.util.HashMap;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.trading.pur.dao.PurReturnOldOrderDao;
import com.hsnn.medstgmini.trading.pur.model.ReturnOrder;
import com.hsnn.medstgmini.trading.pur.model.ReturnStatus;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseDate;

@Repository
public class PurReturnOldOrderDaoImpl implements PurReturnOldOrderDao {

	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	@Autowired
	private HibernateSqlUtil dao;
	
	/**
	 * @category 获取历史退货订单数据[中心端]
	 * 应晓川
	 * @date 2015年7月15日
	 * @return
	 */
	@Override
	public Pagination getReturnOldOrderList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.ret_code retCode, a.hosp_code hospCode, a.hosp_name hospName, ");
		sb.append(" a.detail_count detailCount, a.detail_amount detailAmount,  ");
		sb.append(" DATE_FORMAT(a.ret_datetime, '%Y-%m-%d %T') retDatetime, a.confirm_remark confirmRemark, ");
		sb.append(" a.hosp_ar_fullname areaName, a.del_comp_code delCompCode, ");
		sb.append(" a.del_comp_name delCompName, a.del_comp_name_spel, a.`status`, c.`name` confirmUser, ");
		sb.append(" DATE_FORMAT(a.confirm_time, '%Y-%m-%d %T') confirmTime, b.`name` retUser ");
		sb.append(" FROM pur_returninfo a  ");
		sb.append(" LEFT JOIN sys_user b ON a.ret_user = b.id ");
		sb.append(" LEFT JOIN sys_user c ON a.confirm_user = c.id ");
		
		ReturnOrder returnOrder = (ReturnOrder) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		
		if (returnOrder.getReturnStatus() != null) {
			sb.append(" WHERE a.`status` = :status ");
			params.put("status", returnOrder.getReturnStatus());
		} else {
			sb.append(" WHERE ( a.`status` = :status1 OR a.`status` = :status2 ) ");
			params.put("status1", ReturnStatus.agree.getKey());
			params.put("status2", ReturnStatus.reject.getKey());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getRetCode())) {
			sb.append(" AND a.ret_code = :retCode ");
			params.put("retCode", returnOrder.getRetCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getHospCode())) {
			sb.append(" AND a.hosp_code = :hospCode ");
			params.put("hospCode", returnOrder.getHospCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getHospName())) {
			sb.append(" AND ( a.hosp_name LIKE :hospName OR a.hosp_spel_code LIKE :hospName ) ");
			params.put("hospName", "%" + returnOrder.getHospName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getDelCompName())) {
			sb.append(" AND ( a.del_comp_name LIKE :delCompName OR a.del_comp_name_spel LIKE :delCompName ) ");
			params.put("delCompName", "%" + returnOrder.getDelCompName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getStartDate())) {
			sb.append(" AND DATE_FORMAT(a.ret_datetime, '%Y-%m-%d') >= :startDate ");
			params.put("startDate", returnOrder.getStartDate());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getEndDate())) {
			sb.append(" AND DATE_FORMAT(a.ret_datetime, '%Y-%m-%d') <= :endDate ");
			params.put("endDate", returnOrder.getEndDate());
		}
		sb.append(" ORDER BY a.ret_datetime DESC ");
		return dao.getPageMapList(sb.toString(), params, page);
	}
	/**
	 * 
	 * @category 获取历史退货单列表（医疗机构）
	 * @author 邱磊
	 * @date 2015年8月1日 上午10:46:10
	 * @return
	 */
	public Pagination getReturnOldOrderByHospDataList(Pagination page){
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.ret_code retCode, a.hosp_code hospCode, a.hosp_name hospName, ");
		sb.append(" a.detail_count detailCount, a.detail_amount detailAmount,  ");
		sb.append(" DATE_FORMAT(a.ret_datetime, '%Y-%m-%d %T') retDatetime, a.confirm_remark confirmRemark, ");
		sb.append(" a.hosp_ar_fullname areaName, a.del_comp_code delCompCode, ");
		sb.append(" a.del_comp_name delCompName, a.`status` returnStatus, c.`name` confirmUser, ");
		sb.append(" DATE_FORMAT(a.confirm_time, '%Y-%m-%d %T') confirmTime, b.`name` retUser ");
		sb.append(" FROM pur_returninfo a  ");
		sb.append(" LEFT JOIN sys_user b ON a.ret_user = b.id ");
		sb.append(" LEFT JOIN sys_user c ON a.confirm_user = c.id ");
		sb.append(" WHERE a.hosp_code = :hospCode ");
		
		ReturnOrder returnOrder = (ReturnOrder) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("hospCode", returnOrder.getHospCode());
		
		if (returnOrder.getReturnStatus() != null) {
			sb.append(" AND a.`status` = :status ");
			params.put("status", returnOrder.getReturnStatus());
		} else {
			sb.append(" AND ( a.`status` = :status1 OR a.`status` = :status2 ) ");
			params.put("status1", ReturnStatus.agree.getKey());
			params.put("status2", ReturnStatus.reject.getKey());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getRetCode())) {
			sb.append(" AND a.ret_code = :retCode ");
			params.put("retCode", returnOrder.getRetCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getDelCompName())) {
			sb.append(" AND ( a.del_comp_name LIKE :delCompName OR a.del_comp_name_spel LIKE :delCompName ) ");
			params.put("delCompName", "%" + returnOrder.getDelCompName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getStartDate())) {
			sb.append(" AND a.ret_datetime >= :startDate ");
			params.put("startDate", ParseDate.dateToDateTime(DateUtil.parseDate(returnOrder.getStartDate())));
		}
		
		if (StringUtils.isNotBlank(returnOrder.getEndDate())) {
			sb.append(" AND a.ret_datetime <= :endDate ");
			params.put("endDate", ParseDate.dateToEndDateTime(DateUtil.parseDate(returnOrder.getEndDate())));
		}
		sb.append(" ORDER BY a.ret_datetime DESC ");
		return dao.getPageMapList(sb.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 获取历史退货单列表（配送企业）
	 *@author 邱磊
	 *@date 2015年8月1日 上午10:46:10
	 *@return
	 */
	public Pagination getReturnOldOrderByDelDataList(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("SELECT a.ret_code as retCode,a.hosp_id as hospId,b.hosp_name as hospName,")
		.append("c.name as submitUser,DATE_FORMAT(a.submit_time,'%Y-%m-%d %T') as submitTime,a.status,")
		.append("DATE_FORMAT(a.confirm_time,'%Y-%m-%d %T') as confirmTime,d.name as confirmUser,count(a.drug_id) as retCount,SUM(a.ret_count*a.pro_price) as retMoney from pur_returninfo_old a")
		.append(" left join std_hospinfo b on b.hosp_id=a.hosp_id")
		.append(" left join sys_user c on c.id=a.submit_user")
		.append(" left join sys_user d on d.id=a.confirm_user")
		.append(" where a.del_comp_id=:delCompId");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("delCompId", map.get("delCompId"));
		if (StringUtils.isNotBlank(map.get("retCode"))){
			sql.append(" and a.ret_code=:retCode");
			params.put("retCode", map.get("retCode"));
		}
		if (StringUtils.isNotBlank(map.get("hospName"))){
			sql.append(" and (b.hosp_name like :hospName or b.hosp_spel_code LIKE :hospSpelCode)");
			params.put("hospName", "%"+map.get("hospName").trim()+"%");
			params.put("hospSpelCode", "%"+map.get("hospSpelCode").trim().toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(map.get("beginTime"))){
			sql.append(" and DATE_FORMAT(a.submit_time,'%Y-%m-%d') >= :beginTime");
			params.put("beginTime", map.get("beginTime").trim());
		}
		if (StringUtils.isNotBlank(map.get("endTime"))){
			sql.append(" and DATE_FORMAT(a.submit_time,'%Y-%m-%d') <= :endTime");
			params.put("endTime", map.get("endTime").trim());
		}
		if (StringUtils.isNotBlank(map.get("confirmBeginTime"))){
			sql.append(" and DATE_FORMAT(a.confirm_time,'%Y-%m-%d') >= :confirmBeginTime");
			params.put("confirmBeginTime", map.get("confirmBeginTime").trim());
		}
		if (StringUtils.isNotBlank(map.get("confirmEndTime"))){
			sql.append(" and DATE_FORMAT(a.confirm_time,'%Y-%m-%d') <= :confirmEndTime");
			params.put("confirmEndTime", map.get("confirmEndTime").trim());
		}
		sql.append(" group by a.ret_code order by a.submit_time desc");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取历史退货订单数据[生产企业]
	 * 王炳
	 * @date 2015年7月15日
	 * @return
	 */
	@Override
	public Pagination getProCompReturnOldOrderList(Pagination page) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.ret_count retCount, a.ret_reason retReason,a.status,b.approve_code approveCode, ");
		sb.append(" 	b.qualityspec_name drugName, b.drug_code drugCode, b.form_name formName, ");
		sb.append(" 	b.spec_name specName, b.unit_name unitName, b.material_name materialName, ");
		sb.append(" 	b.factor, b.hosp_code hospCode, b.hosp_name hospName, b.ar_fullname areaName, ");
		sb.append(" 	b.del_comp_code delCompCode, b.del_comp_name delCompName, b.pro_price proPrice, ");
		sb.append(" 	b.total_amount totalAmount,a.drug_medicine drugMedicine, a.ret_code retCode, c.`name` updUser, ");
		sb.append(" 	DATE_FORMAT(a.upd_time, '%Y-%m-%d %T') updTime ");
		sb.append(" FROM pur_returninfodetail a  ");
		sb.append(" JOIN pur_aoginfo b ON a.recp_info_id = b.recp_info_id ");
		sb.append(" LEFT JOIN sys_user c ON a.upd_user = c.id ");
		sb.append(" WHERE b.prod_compcode = :compCode ");

		ReturnOrder returnOrder = (ReturnOrder) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compCode", returnOrder.getCompCode());
		
		if (returnOrder.getReturnStatus() != null) {
			sb.append(" AND a.`status` = :status ");
			params.put("status", returnOrder.getReturnStatus());
		} else {
			sb.append(" AND ( a.`status` = :status1 OR a.`status` = :status2 ) ");
			params.put("status1", ReturnStatus.agree.getKey());
			params.put("status2", ReturnStatus.reject.getKey());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getDrugCode())) {
			sb.append(" AND b.drug_code = :drugCode ");
			params.put("drugCode", returnOrder.getDrugCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getDrugName())) {
			sb.append(" AND ( b.qualityspec_name LIKE :drugName OR b.qualityspec_name_spel LIKE :drugName ) ");
			params.put("drugName", "%" + returnOrder.getDrugName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getHospCode())) {
			sb.append(" AND b.hosp_code = :hospCode ");
			params.put("hospCode", returnOrder.getHospCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getHospName())) {
			sb.append(" AND ( b.hosp_name LIKE :hospName OR b.hosp_spel_code LIKE :hospName ) ");
			params.put("hospName", "%" + returnOrder.getHospName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getAreaId())) {
			sb.append(" AND b.hosp_area_id LIKE :areaId ");
			params.put("areaId", AreaUtil.escapeAreaId(returnOrder.getAreaId()));
		}
		
		if (StringUtils.isNotBlank(returnOrder.getDelCompName())) {
			sb.append(" AND ( b.del_comp_name LIKE :delCompName OR b.del_comp_name_spel LIKE :delCompName ) ");
			params.put("delCompName", "%" + returnOrder.getDelCompName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getStartDate())) {
			sb.append(" AND DATE_FORMAT(a.upd_time, '%Y-%m-%d') >= :startDate ");
			params.put("startDate", returnOrder.getStartDate());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getEndDate())) {
			sb.append(" AND DATE_FORMAT(a.upd_time, '%Y-%m-%d') <= :endDate ");
			params.put("endDate", returnOrder.getEndDate());
		}
		
		sb.append(" ORDER BY a.upd_time DESC ");
		
		return dao.getPageMapList(sb.toString(), params, page);
	}


}
